﻿USE [ReTS_Db]
GO 

PRINT ''
PRINT 'Creating stored procedures for Role table...'

/*
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SelectRoleById]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_SelectRoleById]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_SelectRoleById]
    @Role_Id int
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [Role] WHERE Role_Id = @Role_Id

END
'
GO
*/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InsertRole]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_InsertRole]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_InsertRole]
    @User_Id int,
	@Project_Id int,
	@Role_Type_Id int
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Role] 
	(
	User_Id,
	Project_Id,
	Role_Type_Id
	)
VALUES 
	(
	@User_Id,
	@Project_Id,
	@Role_Type_Id
	)

END
'
GO 

/*
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_UpdateRole]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_UpdateRole]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_UpdateRole]
    @Role_Id int,
    @Title nvarchar(50),
    @Description nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Role] 
SET 
	Title = @Title,
	Description = @Description
WHERE Role_Id = @Role_Id

END
'
GO 
*/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeleteRole]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_DeleteRole]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_DeleteRole]
    @User_Id int,
	@Project_Id int,
	@Role_Type_Id int
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM [Role] 
WHERE User_Id = @User_Id 
	AND Project_Id = @Project_Id
	AND Role_Type_Id = @Role_Type_Id

END
'
GO 